/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.yunhoo.asset.bean;

import com.yunhoo.asset.model.Asset;
import com.yunhoo.sys.db.util.IbatisTemplate;
import com.yunhoo.sys.util.common.LoginParam;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CreationHelper;

@ManagedBean(name = "assetDealBean")
@SessionScoped
public class AssetDealBean {

    private final String NAMESPACE = "psncard.";
    private Asset asset = new Asset();
    private List<Asset> assetlist = new ArrayList<Asset>();
    private int bftotal = 0;
    private int bstotal = 0;
    private Double bfmoney = 0d;
    private Double bsmoney = 0d;

    public AssetDealBean() {
    }

    public void queryList() {
        if (assetlist != null && assetlist.size() > 0) {
            assetlist.clear();
        }
        Map map = new HashMap();
        map.put("orgCode", LoginParam.getOrg().getCode());
        map.put("iyear", LoginParam.getIyear());
        map.put("deptcode", asset.getDept().getCode());
        map.put("cpsn_name", asset.getPerson().getCpsn_name());
        map.put("useStatus", asset.getUseStatus());
        assetlist = IbatisTemplate.queryForList(NAMESPACE + "queryForAssetDeal", map);
        int i = 0;
        int y = 0;
        Double bf = 0d;
        Double bs = 0d;
        for (Asset a : assetlist) {
            if (a.getUseStatus().equals("1")) {
                i++;
                bf += a.getPrice();
            } else {
                y++;
                bs += a.getPrice();
            }
        }
        bftotal = i;
        bstotal = y;
        bfmoney = bf;
        bsmoney = bs;
        System.out.println("Bftotal" + bftotal);
        System.out.println("Bstotal" + bstotal);
        System.out.println("Bfmoney" + bfmoney);
        System.out.println("Bsmoney" + bsmoney);
    }

    public void exportExcel() {
        HSSFWorkbook demoWorkBook = new HSSFWorkbook();
        HSSFSheet demoSheet = demoWorkBook.createSheet("资产处置列表");
        String[] excelColumns = initTabeleHeader();
        List<Asset> rows = assetlist;

        HSSFHeader header = demoSheet.getHeader();
        header.setCenter("资产处置列表");
        HSSFRow headerRow = demoSheet.createRow(0);
        for (int i = 0; i < excelColumns.length; i++) {
            HSSFCell headerCell = headerRow.createCell((short) i);
            //headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);
            headerCell.setCellValue(excelColumns[i]);
        }
        //填充数据
        int rowIndex = 1;
        for (Asset row : rows) {
            createTableRow(demoWorkBook, demoSheet, row, rowIndex, assetlist);
            rowIndex++;
        }
        exportExcel(demoWorkBook, demoSheet);

    }

    public static String convertString(String str) {
        return str == null ? "" : str;
    }

    //初始化表头信息
    public String[] initTabeleHeader() {
        String[] excelColumns = {"序号", "资产编号", "设备名称", "规格型号", "数量", "单价", "金额", "使用状况", "备注"};
        return excelColumns;
    }

    public static void createTableRow(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet, Asset eit, int rowIndex, List assetlist) {
        //创建第rowIndex行
        CreationHelper createHelper = demoWorkBook.getCreationHelper();
        HSSFCellStyle dstyle = demoWorkBook.createCellStyle();
        HSSFCellStyle dstyles = demoWorkBook.createCellStyle();
        HSSFCellStyle dstyled = demoWorkBook.createCellStyle();
        HSSFCellStyle dstyled1 = demoWorkBook.createCellStyle();
        HSSFCellStyle dstyled2 = demoWorkBook.createCellStyle();
        dstyles.setDataFormat(createHelper.createDataFormat().getFormat("yyyy年MM月dd日"));//格式
        dstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.0"));
        dstyled.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        dstyled.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        dstyled1.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        dstyled1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        dstyled1.setBorderTop(HSSFCellStyle.BORDER_NONE);
        dstyled1.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        dstyled1.setBottomBorderColor(HSSFColor.WHITE.index);
        dstyled1.setTopBorderColor(HSSFColor.WHITE.index);
        dstyled2.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
        dstyled2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

        HSSFCell cell = null;
        HSSFRow row = demoSheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(eit.getId());
        cell = row.createCell(1);
        cell.setCellValue(convertString(eit.getAssetCode()));
        cell = row.createCell(2);
        cell.setCellValue(convertString(eit.getAssetName()));
        cell = row.createCell(3);
        cell.setCellValue(convertString(eit.getModel()));
        cell = row.createCell(4);
        cell.setCellValue("1");
        cell = row.createCell(5);
        cell.setCellValue(convertDouble(eit.getPrice()));
        cell.setCellStyle(dstyle);
        cell = row.createCell(6);
        cell.setCellValue(convertDouble(eit.getPrice()));
        cell = row.createCell(7);
        cell.setCellValue(convertString(useStatus().get(eit.getUseStatus())));
        cell = row.createCell(8);
        cell.setCellValue(convertString(eit.getMemo()));

        HSSFCell celld = null;
        HSSFRow rowd = demoSheet.createRow(assetlist.size() + 1);
        demoSheet.addMergedRegion(new Region(assetlist.size() + 1, (short) 0, assetlist.size() + 2, (short) 8));
        celld = rowd.createCell(0);
        celld.setCellValue("处置原因：");
        celld.setCellStyle(dstyled);


        HSSFCell cellds = null;
        HSSFRow rowds = demoSheet.createRow(assetlist.size() + 3);
        demoSheet.addMergedRegion(new Region(assetlist.size() + 3, (short) 0, assetlist.size() + 4, (short) 2));
        demoSheet.addMergedRegion(new Region(assetlist.size() + 3, (short) 3, assetlist.size() + 4, (short) 4));
        demoSheet.addMergedRegion(new Region(assetlist.size() + 3, (short) 5, assetlist.size() + 4, (short) 6));
        demoSheet.addMergedRegion(new Region(assetlist.size() + 3, (short) 7, assetlist.size() + 4, (short) 8));
        cellds = rowds.createCell(0);
        cellds.setCellValue("申请部门意见：");
        cellds.setCellStyle(dstyled);
        cellds = rowds.createCell(3);
        cellds.setCellValue("资产管理部门意见：");
        cellds.setCellStyle(dstyled);
        cellds = rowds.createCell(5);
        cellds.setCellValue("财务部门意见：");
        cellds.setCellStyle(dstyled);
        cellds = rowds.createCell(7);
        cellds.setCellValue("技术部门意见：");
        cellds.setCellStyle(dstyled);

        HSSFCell cellds1 = null;
        HSSFRow rowds1 = demoSheet.createRow(assetlist.size() + 5);
        demoSheet.addMergedRegion(new Region(assetlist.size() + 5, (short) 0, assetlist.size() + 5, (short) 2));
        demoSheet.addMergedRegion(new Region(assetlist.size() + 5, (short) 3, assetlist.size() + 5, (short) 4));
        demoSheet.addMergedRegion(new Region(assetlist.size() + 5, (short) 5, assetlist.size() + 5, (short) 6));
        demoSheet.addMergedRegion(new Region(assetlist.size() + 5, (short) 7, assetlist.size() + 5, (short) 8));
        cellds1 = rowds1.createCell(0);
        cellds1.setCellValue("负责人签字：");
        cellds1.setCellStyle(dstyled1);
        cellds1 = rowds1.createCell(3);
        cellds1.setCellValue("负责人签字：");
        cellds1.setCellStyle(dstyled1);
        cellds1 = rowds1.createCell(5);
        cellds1.setCellValue("负责人签字：");
        cellds1.setCellStyle(dstyled1);
        cellds1 = rowds1.createCell(7);
        cellds1.setCellValue("负责人签字：");
        cellds1.setCellStyle(dstyled1);

        HSSFCell cellds2 = null;
        HSSFRow rowds2 = demoSheet.createRow(assetlist.size() + 6);
        demoSheet.addMergedRegion(new Region(assetlist.size() + 6, (short) 0, assetlist.size() + 6, (short) 2));
        demoSheet.addMergedRegion(new Region(assetlist.size() + 6, (short) 3, assetlist.size() + 6, (short) 4));
        demoSheet.addMergedRegion(new Region(assetlist.size() + 6, (short) 5, assetlist.size() + 6, (short) 6));
        demoSheet.addMergedRegion(new Region(assetlist.size() + 6, (short) 7, assetlist.size() + 6, (short) 8));
        cellds2 = rowds2.createCell(0);
        cellds2.setCellValue("年  月  日");
        cellds2.setCellStyle(dstyled2);
        cellds2 = rowds2.createCell(3);
        cellds2.setCellValue("年  月  日");
        cellds2.setCellStyle(dstyled2);
        cellds2 = rowds2.createCell(5);
        cellds2.setCellValue("年  月  日");
        cellds2.setCellStyle(dstyled2);
        cellds2 = rowds2.createCell(7);
        cellds2.setCellValue("年  月  日");
        cellds2.setCellStyle(dstyled2);

    }

    public static double convertDouble(Double num) {
        return num == null ? 0.0 : num;
    }

    public static Map<String, String> useStatus() {
        Map<String, String> numsta = new HashMap<String, String>();
        numsta.put("0", "在用");
        numsta.put("1", "报废");
        numsta.put("2", "报损");
        numsta.put("3", "其他");
        return numsta;
    }

    public static void exportExcel(HSSFWorkbook demoWorkBook, HSSFSheet demoSheet) {
        try {
            String strfileName = "book.xls";
            FileOutputStream fos = null;
            //导出
            demoSheet.setGridsPrinted(true);//demoSheet
            HSSFFooter footer = demoSheet.getFooter();
            footer.setRight("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
            // 获得JSF上下文环境
            FacesContext context = FacesContext.getCurrentInstance();
            // 获得ServletContext对象
            ServletContext servletContext = (ServletContext) context.getExternalContext().getContext();
            // 取得文件的绝对路径
            String excelName = servletContext.getRealPath("/upload") + "/" + strfileName;//strfileName
            File exportFile = new File(excelName);
            fos = new FileOutputStream(exportFile);//fos
            demoWorkBook.write(fos);//demoWorkBook
            fos.close();

            FacesContext ctx = FacesContext.getCurrentInstance();
            ctx.responseComplete();
            HttpServletResponse httpServletResponse = (HttpServletResponse) ctx.getExternalContext().getResponse();
            ServletOutputStream servletOutputStream = httpServletResponse.getOutputStream();
            httpServletResponse.setHeader("Content-disposition", "attachment; filename=" + strfileName);//strfileName
            httpServletResponse.setContentLength((int) exportFile.length());//exportFile
            httpServletResponse.setContentType("application/x-download");
            //httpServletResponse.setContentType("application/vnd.ms-excel");
            byte[] b = new byte[1024];
            int i = 0;
            FileInputStream fis = new java.io.FileInputStream(exportFile);
            while ((i = fis.read(b)) > 0) {
                servletOutputStream.write(b, 0, i);
            }
            servletOutputStream.flush();
            ctx.responseComplete();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Asset getAsset() {
        return asset;
    }

    public void setAsset(Asset asset) {
        this.asset = asset;
    }

    public List<Asset> getAssetlist() {
        return assetlist;
    }

    public void setAssetlist(List<Asset> assetlist) {
        this.assetlist = assetlist;
    }

    public int getBftotal() {
        return bftotal;
    }

    public void setBftotal(int bftotal) {
        this.bftotal = bftotal;
    }

    public int getBstotal() {
        return bstotal;
    }

    public void setBstotal(int bstotal) {
        this.bstotal = bstotal;
    }

    public Double getBfmoney() {
        return bfmoney;
    }

    public void setBfmoney(Double bfmoney) {
        this.bfmoney = bfmoney;
    }

    public Double getBsmoney() {
        return bsmoney;
    }

    public void setBsmoney(Double bsmoney) {
        this.bsmoney = bsmoney;
    }
}
